﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;

namespace RuiXinCoffee.Utils
{
    public static class DBhelp
    {
        private static string connectionString =
            "server=localhost;user id=root;password=1243969857;database=RuiXinCoffee;";

        public static MySqlConnection GetConnection()
        {
            try
            {
                return new MySqlConnection(connectionString);
            }
            catch (Exception ex)
            {
                throw new DatabaseException("无法创建数据库连接", ex);
            }
        }
        // 执行非查询命令（如INSERT、UPDATE、DELETE），返回受影响的行数
        public static int ExecuteNonQuery(string sql, params MySqlParameter[] parameters)
        {
            try
            {
                using var conn = GetConnection();
                using var cmd = new MySqlCommand(sql, conn);
                if (parameters != null)
                    cmd.Parameters.AddRange(parameters);
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                throw new DatabaseException($"执行SQL命令失败: {sql}", ex);
            }
            catch (Exception ex)
            {
                throw new DatabaseException("执行数据库操作时发生错误", ex);
            }
        }

        // 执行查询，返回DataTable
        public static DataTable ExecuteDataTable(string sql, params MySqlParameter[] parameters)
        {
            try
            {
                using var conn = GetConnection();
                using var cmd = new MySqlCommand(sql, conn);
                if (parameters != null)
                    cmd.Parameters.AddRange(parameters);
                using var adapter = new MySqlDataAdapter(cmd);
                var table = new DataTable();
                adapter.Fill(table);
                return table;
            }
            catch (MySqlException ex)
            {
                throw new DatabaseException($"执行SQL查询失败: {sql}", ex);
            }
            catch (Exception ex)
            {
                throw new DatabaseException("执行数据库查询时发生错误", ex);
            }
        }

        // 执行查询，返回单个值
        public static object ExecuteScalar(string sql, params MySqlParameter[] parameters)
        {
            try
            {
                using var conn = GetConnection();
                using var cmd = new MySqlCommand(sql, conn);
                if (parameters != null)
                    cmd.Parameters.AddRange(parameters);
                conn.Open();
                var executeRow = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return executeRow;
            }
            catch (MySqlException ex)
            {
                throw new DatabaseException($"执行SQL标量查询失败: {sql}", ex);
            }
            catch (Exception ex)
            {
                throw new DatabaseException("执行数据库标量查询时发生错误", ex);
            }
        }
        public static List<T> ExecuteScalarList<T>(string sql, params MySqlParameter[] ps)
        {
            try
            {
                DataTable dt = ExecuteDataTable(sql, ps);
                List<T> ts = new List<T>();
                Type type = typeof(T);
                //用于存储属性的名称
                string tempName = string.Empty;
                foreach (DataRow dr in dt.Rows)
                {
                    //实例化此类
                    T t = Activator.CreateInstance<T>();
                    PropertyInfo[] propertys = type.GetProperties();
                    foreach (PropertyInfo pi in propertys)
                    {
                        tempName = pi.Name;
                        if (dt.Columns.Contains(tempName))
                        {
                            // 判断此属性是否有Setter  
                            if (!pi.CanWrite) continue;
                            object value = dr[tempName];
                            //如果非空，则赋给对象的属性  
                            if (value != DBNull.Value)
                                pi.SetValue(t, value, null);
                        }
                    }
                    ts.Add(t);
                }
                return ts;
            }
            catch (DatabaseException)
            {
                // 重新抛出数据库异常
                throw;
            }
            catch (Exception ex)
            {
                throw new DatabaseException("将数据转换为对象列表时发生错误", ex);
            }
        }
    }
}
